create or replace package admassist.cust_hsd_program as

function is_504
(
   p_pupil_number number,
   p_date_start date,
   p_date_end date
)
return varchar2;
/*
If p_external_codes is null, any external code like 'ELL%' will be selected; otherwise specify the ones that count like so:
   p_external_codes => 'ELL,ELLX'
*/
function is_ell
(
   p_pupil_number number,
   p_date_start date,
   p_date_end date,
   p_external_codes varchar2 default null --comma-separated list
)
return varchar2;
/*
If p_elig_codes is null, any type of free or reduced status counts; otherwise specify the ones that count like so:
   p_elig_codes => 'FM,RD'
*/
function is_farms_lite
(
   p_pupil_number number,
   p_date_start date,
   p_date_end date,
   p_elig_codes varchar2 default null --comma-separated list
)
return varchar2;

function is_sped_lite
(
   p_pupil_number number,
   p_date_start date,
   p_date_end date
)
return varchar2;

end; --package
/
create or replace package body admassist.cust_hsd_program as

function is_504
(
   p_pupil_number number,
   p_date_start date,
   p_date_end date
)
return varchar2
is
l_return varchar2(1);
begin
   select 
      case 
         when 
            exists 
            (
               select 1 from admassist.student_plans p
               where 1=1
                  and p.is_504 = 'Y'
                  and p.pupil_number = p_pupil_number
                  and admassist.cust_hsd_date_util.date_overlaps(p_date_start,p_date_end,p.start_date,nvl(p.end_date,p_date_end)) = 'Y'
                  and rownum = 1
            )
         then 'Y'
         else 'N'
      end into l_return
      from dual;
   return l_return;
end;

function is_ell
(
   p_pupil_number number,
   p_date_start date,
   p_date_end date,
   p_external_codes varchar2 default null --comma-separated list
)
return varchar2
is
l_return varchar2(1);
begin
   select 
      case 
         when exists 
         (
            select 1 
            from 
               admassist.program_assignments pa
               inner join admassist.program_types pt
                  on pt.program_type = pa.program
            where 1=1
               and pa.pupil_number = p_pupil_number
               and admassist.cust_hsd_date_util.date_overlaps(p_date_start,p_date_end,pa.start_date,nvl(pa.end_date,p_date_end)) = 'Y'
               and 
               (
                  (
                     p_external_codes is null
                     and pt.external_code like 'ELL%'
                  )
                  OR
                  (
                     p_external_codes is not null
                     and regexp_instr(p_external_codes,'(^|\W)' || pt.external_code || '(\W|$)') > 0
                  )
               )
               and rownum = 1
         ) then 'Y'
         else 'N'
      end into l_return
      from dual;
   return l_return;
end;

function is_farms_lite
(
   p_pupil_number number,
   p_date_start date,
   p_date_end date,
   p_elig_codes varchar2 default null
)
return varchar2
is
l_return varchar2(1);
begin
   select 
      case 
         when exists 
         (
            select 1 
            from admassist.student_farm_apps_lite pa
            where 1=1
               and pa.pupil_number = p_pupil_number
               and admassist.cust_hsd_date_util.date_overlaps(p_date_start,p_date_end,pa.start_date,nvl(pa.end_date,p_date_end)) = 'Y'
               and 
               (
                  (
                     p_elig_codes is null
                     and pa.eligibility_type_code in ('FM','RD','TF','TR')
                  )
                  OR
                  (
                     p_elig_codes is not null
                     and instr(pa.eligibility_type_code,p_elig_codes) > 0
                  )
               )
               and rownum = 1
         ) then 'Y'
         else 'N'
      end into l_return
      from dual;
   return l_return;
end;

function is_sped_lite
(
   p_pupil_number number,
   p_date_start date,
   p_date_end date
)
return varchar2
is
l_return varchar2(1);
begin
   select 
      case 
         when exists 
         (
            select 1 
            from 
               admassist.sped_exceptionalities sped
               inner join admassist.sped_exceptionality_codes sped_code
                  on sped_code.exceptionality_code = sped.exceptionality_code
            where 1=1
               and sped_code.external_code between '01' and '14'
               and sped.pupil_number = p_pupil_number
               and admassist.cust_hsd_date_util.date_overlaps(p_date_start,p_date_end,sped.start_date,nvl(sped.end_date,p_date_end)) = 'Y'
               and rownum = 1
         ) then 'Y'
         else 'N'
      end into l_return
      from dual;
   return l_return;
end;

end; --package
/
